import os
import sqlite3
import re

from enum import AEnums
import DriveUtil
from mixins import pathMixin


class Transaction(object):
    def __init__(self, sql):
        self.sql = sql

    def __enter__(self):
        self.sql.transaction = True
        pass

    # noinspection PyUnusedLocal
    def __exit__(self, exc_type, value, traceback):
        self.sql.transaction = False
        if exc_type:
            if exc_type == AbortTransaction:
                self.sql.rollback()
                return True
        else:
            self.sql.commit()
            self.sql.resetCache()


class AbortTransaction(Exception):
    pass


# noinspection PyUnresolvedReferences
def getHomedir():
    try:
        from win32com.shell import shellcon, shell
        homedir = shell.SHGetFolderPath(0, shellcon.CSIDL_APPDATA, 0, 0)
    except ImportError:  # quick semi-nasty fallback for non-windows/win32com case
        homedir = os.path.expanduser("~")
    homedir += '/AnyBackup'
    return homedir


def buildAnyBackupDB(sql):
    c = sql.cursor()
    c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(
        AEnums.VALIDEXTS, AEnums.EXT, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(
        AEnums.SKIPLIST, AEnums.SKIP, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} real, {5} real,
                {6} text, {7} text, {8} text, {9} text, {10} integer, {11} text,
                PRIMARY KEY ({1}))'''.format(
        AEnums.FILES, AEnums.ID, AEnums.NAME, AEnums.PATH,
        AEnums.MTIME, AEnums.SIZE, AEnums.SERIAL,
        AEnums.VOLNAME, AEnums.PARENT, AEnums.PREFIX, AEnums.DIR,
        AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
                {4} real, {5} real, {6} text, {7} text, {8} text,
                PRIMARY KEY ({1}, {2}, {6}))'''.format(AEnums.DRIVES, AEnums.NAME,
                                                       AEnums.SERIAL, AEnums.TYPE, AEnums.FREESPACE,
                                                       AEnums.TOTALSPACE,
                                                       AEnums.SETNAME, AEnums.REMOTEHOST,
                                                       AEnums.REMOTEPATH))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
              PRIMARY KEY ({1},{2},{3}))'''.format(
        AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
                PRIMARY KEY ({1},{2},{3},{4}))'''.format(
        AEnums.PATHS, AEnums.PATH, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME
    ))
    c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
            PRIMARY KEY ({1}))'''.format(
        AEnums.ROOT, AEnums.ID, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME,
    ))
    c.execute('''CREATE TABLE {0} ({1} text, PRIMARY KEY ({1}))'''.format(
        AEnums.SETS, AEnums.SETNAME
    ))
    c.execute('''INSERT INTO {0} VALUES (?)'''.format(
        AEnums.SETS), ['AnyBackup']
    )
    c.execute('''PRAGMA journal_mode=OFF''')
    c.execute('''PRAGMA synchronous=OFF''')
    c.execute('''PRAGMA temp_store=2''')
    c.execute('''PRAGMA count_changes=OFF''')
    sql.commit()


class SQLLite(pathMixin):
    sql = None
    cache = {}

    # noinspection PyArgumentList
    def __init__(self):
        self.transaction = False
        self.homedir = getHomedir()
        if not self.sql:
            init = not os.path.exists('%s/anybackup.db' % self.homedir)
            self.sql = sqlite3.connect('%s/anybackup.db' % self.homedir, check_same_thread=False)
            if init:
                buildAnyBackupDB(self.sql)

    def commit(self):
        self.sql.commit()

    def rollback(self):
        self.sql.rollback()

    def batch(self):
        return Transaction(self)

    def resetCache(self):
        self.cache = {}
        for backupSet in self.getSets():
            for drive in self.getDrives(AEnums.BACKUP, backupSet) + self.getDrives(AEnums.CONTENT, backupSet):
                for fileRef in self.getDriveFiles(drive):
                    p = fileRef[AEnums.PARENT]
                    if p not in self.cache:
                        self.cache[p] = []
                    self.cache[p].append(fileRef)

    def addValidExt(self, ext, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [ext, backupSet])
        if not self.transaction:
            self.sql.commit()

    def setValidExt(self, ext, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [backupSet]
        )
        if type(ext) != str:
            lst = ext
        else:
            lst = [ext]
        for ext in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.VALIDEXTS),
                      [ext, backupSet])
        if not self.transaction:
            self.sql.commit()

    def addSkip(self, skip, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST), [skip, backupSet])
        if not self.transaction:
            self.sql.commit()

    def setSkip(self, skip, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST, AEnums.SETNAME), [backupSet]
        )
        if type(skip) != str:
            lst = skip
        else:
            lst = [skip]
        for skip in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST),
                      [skip, backupSet])
        if not self.transaction:
            self.sql.commit()

    def getSets(self):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1}'''.format(
            AEnums.SETNAME, AEnums.SETS,
        ))
        return [x[0] for x in c.fetchall()]

    def addSet(self, backupSet):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?)'''.format(
            AEnums.SETS), [backupSet]
        )
        if not self.transaction:
            self.sql.commit()

    def removeSet(self, backupSet):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SETS, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.FILES, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.DRIVES, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.LOCKED, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.ROOT, AEnums.SETNAME), [backupSet],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.PATHS, AEnums.SETNAME), [backupSet],
        )
        if not self.transaction:
            self.sql.commit()

    def getValidExt(self, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.EXT, AEnums.VALIDEXTS, AEnums.SETNAME),
            [backupSet],
        )
        return [x[0] for x in c.fetchall()]

    def getSkipList(self, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.SKIP, AEnums.SKIPLIST, AEnums.SETNAME),
            [backupSet],
        )
        return [x[0] for x in c.fetchall()]

    def getDrive(self, name, serial, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME), [name, serial, backupSet])
        ret = c.fetchone()
        if ret:
            return {
                AEnums.NAME: ret[0],
                AEnums.SERIAL: ret[1],
                AEnums.TYPE: ret[2],
                AEnums.FREESPACE: ret[3],
                AEnums.TOTALSPACE: ret[4],
                AEnums.SETNAME: ret[5],
                AEnums.REMOTEHOST: ret[6],
                AEnums.REMOTEPATH: ret[7],
            }
        return None

    def getDriveByFile(self, fileRef):
        return self.getDrive(fileRef[AEnums.VOLNAME],
                             fileRef[AEnums.SERIAL],
                             fileRef[AEnums.SETNAME])

    def updateDrive(self, dd):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME]],
        )
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.TYPE],
                dd[AEnums.FREESPACE], dd[AEnums.TOTALSPACE], dd[AEnums.SETNAME],
                dd.get(AEnums.REMOTEHOST, ''), dd.get(AEnums.REMOTEPATH, ''), ]
        )
        if not self.transaction:
            self.sql.commit()

    def removeFiles(self, drives):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
                ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
                ])
        if not self.transaction:
            self.sql.commit()

    def updateFreeSpace(self, d):
        letter = DriveUtil.getLetter(d[AEnums.NAME], d[AEnums.SERIAL])
        if not letter:
            #print "Cannot update freespace for " + self._name + " when drive is not connected!\n"
            return False
        freeSpace = DriveUtil.getFreeSpace(letter)
        totalSpace = DriveUtil.getTotalSpace(letter)
        d[AEnums.FREESPACE] = freeSpace
        d[AEnums.TOTALSPACE] = totalSpace
        return self.updateDrive(d)

    def getDriveFiles(self, d):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
            [d[AEnums.NAME], d[AEnums.SERIAL], d[AEnums.SETNAME]])
        raw = c.fetchall()
        return self.convertFiles(raw)

    def isLocked(self, drive):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        return bool(c.fetchall())

    def findDir(self, drive, path):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
            AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.PATH),
            [
                drive[AEnums.NAME], drive[AEnums.SETNAME], drive[AEnums.SERIAL], path + '%',
            ])
        return bool(c.fetchall())

    def getFile(self, fildId):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=?'''.format(
            AEnums.FILES, AEnums.ID), [fildId]
        )
        results = c.fetchone()
        if results:
            return {
                AEnums.ID: results[0],
                AEnums.NAME: results[1],
                AEnums.PATH: results[2],
                AEnums.MTIME: results[3],
                AEnums.SIZE: results[4],
                AEnums.SERIAL: results[5],
                AEnums.VOLNAME: results[6],
                AEnums.PARENT: results[7],
                AEnums.PREFIX: results[8],
                AEnums.DIR: bool(results[9]),
                AEnums.SETNAME: results[10],
            }

    def getFullPaths(self, files, driveType=AEnums.CONTENT):
        letters = {}
        paths = []
        for fileRef in files:
            k = (fileRef[AEnums.VOLNAME], fileRef[AEnums.SERIAL])
            if not k in letters:
                letters[k] = DriveUtil.getLetter(*k)
            if not letters[k]:
                paths.append(None)
            else:
                if driveType == AEnums.CONTENT:
                    paths.append(self.createPath(letters[k] + ':', fileRef[AEnums.PATH], fileRef[AEnums.NAME]))
                else:
                    paths.append(self.createPath(letters[k] + ':', fileRef[AEnums.SETNAME], fileRef[AEnums.PATH],
                                                 fileRef[AEnums.NAME]))
        return paths

    def getFullPath(self, fileRef):
        drive = self.getDriveByFile(fileRef)
        paths = self.getFullPaths([fileRef], drive[AEnums.TYPE])
        return paths[0]

    def getPaths(self, drive):
        c = self.sql.cursor()
        c.execute('''SELECT {0} from {1} WHERE {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATH, AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        ret = c.fetchall()
        return [x[0] for x in ret]

    def addPath(self, drive, path):
        returnCode = True
        letter = DriveUtil.getLetter(drive[AEnums.NAME], drive[AEnums.SERIAL])
        newPath = re.sub(r'^%s:' % letter, '', path)
        if not re.search(r'%s$' % re.escape(os.sep), newPath, re.IGNORECASE):
            newPath += os.sep
        result = self.containsPath(drive, newPath)
        if result == AEnums.OVERWRITE:
            self.containsPath(drive, newPath, overwrite=True)
            returnCode = AEnums.OVERWRITE
        elif result:
            return False
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
            AEnums.PATHS),
            [newPath, drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]]
        )
        if not self.transaction:
            self.sql.commit()
        return returnCode

    def containsPath(self, drive, path, overwrite=False):
        for directory in self.getPaths(drive):
            oldPath = directory
            if self._containsPath(oldPath, path):
                return True
            elif self._containsPath(path, oldPath):
                if overwrite:
                    self.removePath(drive, oldPath)
                return AEnums.OVERWRITE
        return False

    def _containsPath(self, dirName, subdir):
        if dirName.endswith(os.sep):
            dirName = dirName[0:-1]
        if subdir.endswith(os.sep):
            subdir = subdir[0:-1]
        head, tail = os.path.split(subdir)
        while head and tail:
            if head == dirName:
                return True
            head, tail = os.path.split(head)
        else:
            return False

    def removePath(self, drive, path):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.PATH, AEnums.SETNAME),
            [drive[AEnums.NAME], drive[AEnums.SERIAL], path, drive[AEnums.SETNAME]],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
            AEnums.FILES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.PATH),
            [drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME], path + '%'],
        )

    def setRoot(self, drive, rootFiles):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
            ])
        for fileRef in rootFiles:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT), [fileRef, drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]]
            )
        if not self.transaction:
            self.sql.commit()

    def getFiles(self, name, dirs=False, cache=True):
        if cache and name in self.cache:
            return self.cache[name]
        c = self.sql.cursor()
        if dirs:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.FILES, AEnums.PARENT, AEnums.DIR), [name, 1]
            )
        else:
            c.execute('''SELECT * FROM {0} WHERE {1}=?'''.format(
                AEnums.FILES, AEnums.PARENT), [name]
            )
        ret = c.fetchall()
        ret = self.convertFiles(ret)
        return ret

    def getRootFile(self, drive, dirs=False):
        c = self.sql.cursor()
        if dirs:
            c.execute('''SELECT * FROM {0} WHERE {1}=? {2} in (SELECT {2} FROM {3} WHERE {4}=? AND {5}=?)'''.format(
                AEnums.FILES, AEnums.ID, AEnums.DIR, AEnums.ROOT, AEnums.NAME, AEnums.SERIAL),
                [1, drive[AEnums.NAME], drive[AEnums.SERIAL]],
            )
        else:
            c.execute('''SELECT * FROM {0} WHERE {1} in (SELECT {1} FROM {2} WHERE {3}=? AND {4}=?)'''.format(
                AEnums.FILES, AEnums.ID, AEnums.ROOT, AEnums.NAME, AEnums.SERIAL),
                [drive[AEnums.NAME], drive[AEnums.SERIAL]],
            )
        return self.convertFiles(c.fetchall())

    def addDrive(self, dd, paths=None):
        c = self.sql.cursor()
        if AEnums.FREESPACE not in dd:
            dd[AEnums.FREESPACE] = 1
        if AEnums.TOTALSPACE not in dd:
            dd[AEnums.TOTALSPACE] = 1
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME],
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? and {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME],
            ])
        if not paths:
            paths = [os.sep]
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [
                dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.TYPE],
                dd[AEnums.FREESPACE], dd[AEnums.TOTALSPACE], dd[AEnums.SETNAME],
                dd.get(AEnums.REMOTEHOST, ''), dd.get(AEnums.REMOTEPATH, ''),
            ])
        for path in paths:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.PATHS),
                [
                    path, dd[AEnums.NAME], dd[AEnums.SERIAL], dd[AEnums.SETNAME]
                ])
        if not self.transaction:
            return self.sql.commit()

    def addFile(self, drive, fd, root=False):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?,?,?,?)'''.format(
            AEnums.FILES),
            [
                fd[AEnums.ID], fd[AEnums.NAME], fd[AEnums.PATH],
                fd[AEnums.MTIME], fd[AEnums.SIZE], fd[AEnums.SERIAL],
                fd[AEnums.VOLNAME], fd[AEnums.PARENT],
                fd[AEnums.PREFIX], int(fd[AEnums.DIR]), fd[AEnums.SETNAME],
            ])
        if root:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT),
                [
                    fd[AEnums.ID], drive[AEnums.NAME],
                    drive[AEnums.SERIAL], fd[AEnums.SETNAME],
                ])
        if not self.transaction:
            self.sql.commit()

    def commit(self):
        self.sql.commit()

    def removeDrive(self, drive):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME),
            [
                drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME]
            ])
        if not self.transaction:
            return self.sql.commit()

    def search(self, regex, backupSet):
        c = self.sql.cursor()
        d = regex[AEnums.DRIVE]
        if d:
            if regex[AEnums.TYPE] != AEnums.ALL:
                c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=? AND {5} LIKE ?'''.format(
                    AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.DIR, AEnums.NAME),
                    [
                        d[AEnums.NAME], d[AEnums.SERIAL], backupSet, int(regex[AEnums.TYPE] == AEnums.DIR),
                        regex[AEnums.QUERY],
                    ])
            else:
                c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
                    AEnums.FILES, AEnums.VOLNAME, AEnums.SERIAL, AEnums.SETNAME, AEnums.NAME),
                    [
                        d[AEnums.NAME], d[AEnums.SERIAL], backupSet, regex[AEnums.QUERY],
                    ])
        elif regex[AEnums.TYPE] != AEnums.ALL:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3} LIKE ?'''.format(
                AEnums.FILES, AEnums.SETNAME, AEnums.DIR, AEnums.NAME),
                [
                    backupSet, int(regex[AEnums.TYPE] == AEnums.DIR), regex[AEnums.QUERY],
                ])
        else:
            c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2} LIKE ?'''.format(
                AEnums.FILES, AEnums.SETNAME, AEnums.NAME),
                [
                    backupSet, regex[AEnums.QUERY],
                ])
        ret = c.fetchall()
        return self.convertFiles(ret)

    def convertFiles(self, ret):
        files = []
        for fileRef in ret:
            files.append({
                AEnums.ID: fileRef[0],
                AEnums.NAME: fileRef[1],
                AEnums.PATH: fileRef[2],
                AEnums.MTIME: fileRef[3],
                AEnums.SIZE: fileRef[4],
                AEnums.SERIAL: fileRef[5],
                AEnums.VOLNAME: fileRef[6],
                AEnums.PARENT: fileRef[7],
                AEnums.PREFIX: fileRef[8],
                AEnums.DIR: bool(fileRef[9]),
                AEnums.SETNAME: fileRef[10],
            })
        return files

    def removeFile(self, name, backupSet):
        files = self.getFiles(name, backupSet)
        c = self.sql.cursor()
        for fileRef in files:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.FILES, AEnums.ID, AEnums.SETNAME),
                [
                    fileRef[AEnums.ID], fileRef[AEnums.SETNAME],
                ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.ROOT, AEnums.ID, AEnums.SETNAME),
                [
                    fileRef[AEnums.ID], fileRef[AEnums.SETNAME],
                ])
        if not self.transaction:
            self.sql.commit()

    def lockDrives(self, drives, isLock):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.LOCKED, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME),
                [
                    drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
                ])
            if isLock:
                c.execute('''INSERT INTO {0} VALUES (?,?,?)'''.format(
                    AEnums.LOCKED),
                    [
                        drive[AEnums.NAME], drive[AEnums.SERIAL], drive[AEnums.SETNAME],
                    ])
        if not self.transaction:
            self.sql.commit()

    def getDrives(self, driveType, backupSet):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
            AEnums.DRIVES, AEnums.TYPE, AEnums.SETNAME),
            [driveType, backupSet])
        ret = c.fetchall()
        drives = []
        for drive in ret:
            drives.append({
                AEnums.NAME: drive[0],
                AEnums.SERIAL: drive[1],
                AEnums.TYPE: drive[2],
                AEnums.FREESPACE: drive[3],
                AEnums.TOTALSPACE: drive[4],
                AEnums.SETNAME: drive[5],
                AEnums.REMOTEHOST: drive[6],
                AEnums.REMOTEPATH: drive[7],
            })
        return drives